<?php

namespace app\advertisement\logic;

use app\api\logic\BasicLogic;
use think\facade\Db;
use think\facade\Cache;
use util\Excel;
use think\exception\ValidateException;
use think\facade\Log;
use util\Token;
use util\Upload;
use think\cache\driver\Redis;

class ExpertLogic extends BasicLogic
{
    protected $model = null;

    public function __construct()
    {
        $this->model = new \app\advertisement\model\Advplatform();
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 列表
     * @author zhangheng
     */
    public function index($params = [])
    {
        $limit = $params['limit'] ?? 10;
        $date = $params['date'] ?? "";
        $name = $params['name'] ?? "";
        $shop_id = $params['shop_id'] ?? "";
        $tabstatus = $params['tabstatus'] ?? "";

        $where = [];
        if (!empty($date)) {
            // $where[] = ['stat_time_day', '>=', $date['0']];
            // $where[] = ['stat_time_day', '<=', $date['1']];
        }
        
        if (!empty($name)) {
            $where[]   =  ['adv_name', 'like', '%' . $name . '%'];
        }
        if (!empty($shop_id)) {
            $where[]   =  ['shop_id', '=', $shop_id];
        }
        $where[]   =  ['kpop_code', '<>', ''];
        if (!empty($tabstatus)) {
            switch ($tabstatus) {
                case '1':
                    $where[]   =  ['adv_type', '=', $tabstatus];
                    break;
                case '2':
                    $where[]   =  ['adv_type', '=', $tabstatus];
                    break;
                default:
            }
        }

        $list = Db::name('adv_campaignsinfo')
            ->field('
                SUM(spend) as spend,
                SUM(cpm) as cpm,
                SUM(website_ctr) as website_ctr,
                SUM(website_purchase_roas) as website_purchase_roas,
                SUM(video_play_actions) as video_play_actions,
                SUM(video_avg_time_watched_actions) as video_avg_time_watched_actions,
                SUM(video_continuous_2_sec_watched_actions) as video_continuous_2_sec_watched_actions,
                SUM(video_30_sec_watched_actions) as video_30_sec_watched_actions,
                SUM(video_time_watched_actions) as video_time_watched_actions,
                SUM(video_p25_watched_actions) as video_p25_watched_actions,
                SUM(video_p50_watched_actions) as video_p50_watched_actions,
                SUM(video_p100_watched_actions) as video_p100_watched_actions,
                SUM(video_play_retention_0_to_15s_actions) as video_play_retention_0_to_15s_actions,
                SUM(video_play_retention_20_to_60s_actions) as video_play_retention_20_to_60s_actions,
                shop_id,adv_type,adv_name,api_status,kpop_code
            ')
            ->group('kpop_code')
            ->where($where)
            ->paginate($limit);
        $lists = $list->items();

        $shopRecords = Db::name('shops')
            ->where('status',1)
            ->field("id,name")->select()->toarray();
        $shops = array_column($shopRecords, 'name','id');
        // halt($shops);
        foreach ($lists as &$v) {
            $v['shop_id'] = $v['shop_id'] ?? 0;
            // halt($v['shop_id']);
            $v['shop_name'] = $shops[$v['shop_id']] ?? '未归属';
            switch ($v['adv_type']) {
                case '2':
                    $v['adv_type'] = '达人';
                    break;
                case '1':
                    $v['adv_type'] = '混剪';
                    break;
                default:
                    $v['adv_type'] = '默认';
            }
            if(!empty($v['spend'])){
                $v['spend'] = number_format($v['spend'], 0,".","");
            }
            if(!empty($v['cpm'])){
                $v['cpm'] = number_format($v['cpm'], 0,".","");
            }
            if(!empty($v['website_ctr'])){
                $v['website_ctr'] = number_format($v['website_ctr'], 0,".","");
            }
            if(!empty($v['website_purchase_roas'])){
                $v['website_purchase_roas'] = number_format($v['website_purchase_roas'], 0,".","");
            }
            if(!empty($v['video_play_actions'])){
                $v['video_play_actions'] = number_format($v['video_play_actions'], 0,".","");
            }
            if(!empty($v['video_avg_time_watched_actions'])){
                $v['video_avg_time_watched_actions'] = number_format($v['video_avg_time_watched_actions'], 0,".","");
            }
            if(!empty($v['video_continuous_2_sec_watched_actions'])){
                $v['video_continuous_2_sec_watched_actions'] = number_format($v['video_continuous_2_sec_watched_actions'], 0,".","");
            }
            if(!empty($v['video_30_sec_watched_actions'])){
                $v['video_30_sec_watched_actions'] = number_format($v['video_30_sec_watched_actions'], 0,".","");
            }
            if(!empty($v['video_time_watched_actions'])){
                $v['video_time_watched_actions'] = number_format($v['video_time_watched_actions'], 0,".","");
            }
            if(!empty($v['video_p25_watched_actions'])){
                $v['video_p25_watched_actions'] = number_format($v['video_p25_watched_actions'], 0,".","");
            }
            if(!empty($v['video_p50_watched_actions'])){
                $v['video_p50_watched_actions'] = number_format($v['video_p50_watched_actions'], 0,".","");
            }
            if(!empty($v['video_p100_watched_actions'])){
                $v['video_p100_watched_actions'] = number_format($v['video_p100_watched_actions'], 0,".","");
            }
            if(!empty($v['video_play_retention_0_to_15s_actions'])){
                $v['video_play_retention_0_to_15s_actions'] = number_format($v['video_play_retention_0_to_15s_actions'], 0,".","");
            }
            if(!empty($v['video_play_retention_20_to_60s_actions'])){
                $v['video_play_retention_20_to_60s_actions'] = number_format($v['video_play_retention_20_to_60s_actions'], 0,".","");
            }
        }
        
        $data = [
            'list' => $lists,
            'total' => $list->total(),
        ];
        return json(DefaultResponse(200, true, '获取成功', $data));
    }

    /**
    * @desc    find
    * @author  zhangheng
    */
    public function find($params)
    {
        $kpop_code = $params['kpop_code'] ?? 0;
        if  (empty($kpop_code)) {
            return json(DefaultResponse(400, false, '参数错误'));
        }
        $data = Db::name('adv_campaignsinfo')
            ->field('
                SUM(spend) as spend,
                SUM(cpm) as cpm,
                SUM(website_ctr) as website_ctr,
                SUM(website_purchase_roas) as website_purchase_roas,
                SUM(video_play_actions) as video_play_actions,
                SUM(video_avg_time_watched_actions) as video_avg_time_watched_actions,
                SUM(video_continuous_2_sec_watched_actions) as video_continuous_2_sec_watched_actions,
                SUM(video_30_sec_watched_actions) as video_30_sec_watched_actions,
                SUM(video_time_watched_actions) as video_time_watched_actions,
                SUM(video_p25_watched_actions) as video_p25_watched_actions,
                SUM(video_p50_watched_actions) as video_p50_watched_actions,
                SUM(video_p100_watched_actions) as video_p100_watched_actions,
                SUM(video_play_retention_0_to_15s_actions) as video_play_retention_0_to_15s_actions,
                SUM(video_play_retention_20_to_60s_actions) as video_play_retention_20_to_60s_actions,
                shop_id,adv_type,adv_name,api_status,kpop_code
            ')
            ->where('kpop_code',$kpop_code)
            ->find();
        return json(DefaultResponse(200, true, '获取成功', $data));

    }

    /**
    * @desc    add
    * @author  zhangheng
    */
    public function add($params)
    {
        $id = $params['id'] ?? 0;
        $title = $params['title'] ?? 0;
        
        if(empty($id)) {
            return json(DefaultResponse(400, false, '参数错误'));
        }
        $adv_campaigns = Db::name('adv_campaigns')->where('campaign_id',$id)->find();
        if(!empty($adv_campaigns)) {
            return json(DefaultResponse(400, false, '已有广告系列'));
        }
        $insert = [
            'campaign_id' => $id,
            'name' => $title,
            'createtime' => date('Y-m-d H:i:s'),
            'updatetime' => date('Y-m-d H:i:s'),
        ];
        $data = Db::name('adv_campaigns')->insert($insert);
        return json(DefaultResponse(200, true, '添加成功', $data));

    }

    

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 导出
     * @author zhangheng
     */
    public function export($params = [])
    {
        $date = $params['date'] ?? "";
        $name = $params['name'] ?? "";
        $shop_id = $params['shop_id'] ?? "";
        $tabstatus = $params['tabstatus'] ?? "";

        $where = [];
        if (!empty($date)) {
            $where[] = ['stat_time_day', '>=', $date['0']];
            $where[] = ['stat_time_day', '<=', $date['1']];
        }
        
        if (!empty($name)) {
            $where[]   =  ['adv_name', 'like', '%' . $name . '%'];
        }
        if (!empty($shop_id)) {
            $where[]   =  ['shop_id', '=', $shop_id];
        }
        if (!empty($tabstatus)) {
            switch ($tabstatus) {
                case '1':
                    $where[]   =  ['adv_type', '=', $tabstatus];
                    break;
                case '2':
                    $where[]   =  ['adv_type', '=', $tabstatus];
                    break;
                default:
            }
            
        }
        $lists = Db::name('adv_campaignsinfo')
            ->where($where)
            ->select()
            ->toarray();

        $shopRecords = Db::name('shops')
            ->where('status',1)
            ->field("id,name")->select()->toarray();
        $shops = array_column($shopRecords, 'name','id');
        // halt($shops);
        foreach ($lists as &$v) {
            $v['shop_id'] = $v['shop_id'] ?? 0;
            // halt($v['shop_id']);
            $v['shop_name'] = $shops[$v['shop_id']] ?? '未归属';
            switch ($v['adv_type']) {
                case '2':
                    $v['adv_type'] = '达人';
                    break;
                case '1':
                    $v['adv_type'] = '混剪';
                    break;
                default:
                    $v['adv_type'] = '默认';
            }
        }

        $header = [];
        $header[] = ['广告状态', 'api_status'];
        $header[] = ['归属店铺', 'shop_name'];
        $header[] = ['系列名', 'adv_name'];
        $header[] = ['类型', 'adv_type'];
        $header[] = ['广告消耗', 'spend'];
        $header[] = ['日期', 'stat_time_day'];
        $header[] = ['cpm', 'cpm'];
        $header[] = ['ctr点击率', 'website_ctr'];
        $header[] = ['ROI', 'website_purchase_roas'];
        // $header[] = ['转换价值', 'conversion_values'];
        $header[] = ['视频播放次数', 'video_play_actions'];
        $header[] = ['平均播放', 'video_avg_time_watched_actions'];
        $header[] = ['两秒播放', 'video_continuous_2_sec_watched_actions'];
        $header[] = ['30秒播放', 'video_30_sec_watched_actions'];
        $header[] = ['播放时长次数', 'video_time_watched_actions'];
        $header[] = ['25%播放', 'video_p25_watched_actions'];
        $header[] = ['50%播放', 'video_p50_watched_actions'];
        $header[] = ['100%播放', 'video_p100_watched_actions'];
        $header[] = ['0-15s留存', 'video_play_retention_0_to_15s_actions'];
        $header[] = ['20-60s留存', 'video_play_retention_20_to_60s_actions'];

        $fileName = "export_Advplatform" . time();
        return Excel::exportData($lists, $header, $fileName, 'xlsx');
    }
    
    
}
